const mysql=require('mysql');
const express = require('express');
const router = express.Router();
const bodyParser = require('body-parser');
const connection = mysql.createPool({
       host:'localhost',
       user:'root',
       password:'123qweASD',
       database:'HaiShi',
       connectionLimit:200
   }
)



const XLSX = require('xlsx');


const multer  = require('multer');
const upload = multer({ dest: '/tmp/'});
const fs = require('fs');
const uuid = require('uuid');


const cros = function(req, res,next) {
    /**
     * Response settings
     * @type {Object}
     */
    var responseSettings = {
        "AccessControlAllowOrigin": req.headers.origin,
        "AccessControlAllowHeaders": "Content-Type,X-CSRF-Token, X-Requested-With, Accept, Accept-Version, Content-Length, Content-MD5,  Date, X-Api-Version, X-File-Name",
        "AccessControlAllowMethods": "POST, GET, PUT, DELETE, OPTIONS",
        "AccessControlAllowCredentials": true
    };

    /**
     * Headers
     */
    res.header("Access-Control-Allow-Credentials", responseSettings.AccessControlAllowCredentials);
    res.header("Access-Control-Allow-Origin",  responseSettings.AccessControlAllowOrigin);
    res.header("Access-Control-Allow-Headers", (req.headers['access-control-request-headers']) ? req.headers['access-control-request-headers'] : "x-requested-with");
    res.header("Access-Control-Allow-Methods", (req.headers['access-control-request-method']) ? req.headers['access-control-request-method'] : responseSettings.AccessControlAllowMethods);

    // if ('OPTIONS' == req.method) {
    //     res.send(200);
    // }
    // else {
    //     next();
    // }
}

/* GET home page. */
router.get('/Users', function(req, res, next) {
    cros(req,res,next);
// connection.connect();
  connection.query('select * from `User`',function(err,rows,fields) {
     var ret = {};
        if(err) {
            ret.IsSuccess = false;
            ret.Message = err;
            ret.Data = null;
        } else {
            ret.IsSuccess = true;
            ret.Message = '';
            ret.Data = rows;
        }
        var jsonObj = JSON.stringify(ret);
        res.send(jsonObj);
    //   connection.end();
  });
});

// 新建用户
router.post('/Users',function(req,res,next){
    cros(req,res,next);
    var sqlstr = 'insert into `User`(UserName,RealName,Password,IsAdmin) values(?,?,md5(?),?)';
    var params = [];
    var userName = req.body.UserName;
    var password = req.body.Password;
    var realName = req.body.RealName;
    var isAdmin = req.body.IsAdmin;
    if(!isAdmin) {
        isAdmin = 0;
    }
    params.push(userName);
    params.push(realName);
    params.push(password);
    params.push(isAdmin);
    console.dir(params);
    sqlstr = mysql.format(sqlstr,params);
    console.log(sqlstr);
    connection.query(sqlstr,function (err,result) {
        var ret = {};
        if(err) {
            ret.IsSuccess = false;
            ret.Message = err;
            ret.Data = null;
        } else {
            ret.IsSuccess = true;
            ret.Message = '';
            ret.Data = {};
            ret.Data.UID = result.insertId;
        }
        var jsonObj = JSON.stringify(ret);
        
        
        res.send(jsonObj);
    });
});

router.all('*', function(req, res,next) {
    /**
     * Response settings
     * @type {Object}
     */
    var responseSettings = {
        "AccessControlAllowOrigin": req.headers.origin,
        "AccessControlAllowHeaders": "Content-Type,X-CSRF-Token, X-Requested-With, Accept, Accept-Version, Content-Length, Content-MD5,  Date, X-Api-Version, X-File-Name",
        "AccessControlAllowMethods": "POST, GET, PUT, DELETE, OPTIONS",
        "AccessControlAllowCredentials": true
    };

    /**
     * Headers
     */
    res.header("Access-Control-Allow-Credentials", responseSettings.AccessControlAllowCredentials);
    res.header("Access-Control-Allow-Origin",  responseSettings.AccessControlAllowOrigin);
    res.header("Access-Control-Allow-Headers", (req.headers['access-control-request-headers']) ? req.headers['access-control-request-headers'] : "x-requested-with");
    res.header("Access-Control-Allow-Methods", (req.headers['access-control-request-method']) ? req.headers['access-control-request-method'] : responseSettings.AccessControlAllowMethods);

    if ('OPTIONS' == req.method) {
        res.send(200);
    }
    else {
        next();
    }


});

// 登录函数
router.post('/Login',function(req,res,next) {
    cros(req,res,next);
    var sqlstr = 'select * from `User` where UserName=? and Password=md5(?)';
    var params = [];
    var userName = req.body.UserName;
    var password = req.body.Password;
    params.push(userName);
    params.push(password);
    sqlstr = mysql.format(sqlstr,params);
    connection.query(sqlstr,function (err,rows,fields) {
        var ret = {};
        if(err) {
            ret.IsSuccess = false;
            ret.Message = err;
            ret.Data = null;
        } else {
            if(rows.length >0) {
                ret.IsSuccess = true;
                ret.Message = '';
                ret.Data = {};
                ret.Data.IsLogined = true;
                ret.Data.IsAdmin = rows[0]['IsAdmin'];
            } else {
                ret.IsSuccess = true;
                ret.Message = '';
                ret.Data = {};
                ret.Data.IsLogined = false;
                ret.Data.IsAdmin = 0;
            }
            
        }
        var jsonObj = JSON.stringify(ret);
        
        res.send(jsonObj);
    });
})

// 修改用户基本信息
router.put('/Users',function(req,res,next) {
    cros(req,res,next);
    var sqlstr = 'update `User` set UserName=ifnull(?,UserName),RealName=ifnull(?,RealName),`Password`=ifnull(md5(?),Password) where UID=?';
    var params = [];
    var userName = req.body.UserName;
    var password = req.body.Password;
    var realName = req.body.RealName;
    var uid = req.body.UID;
    params.push(userName);
    params.push(realName);
    params.push(password);
    params.push(uid);
    console.dir(params);
    sqlstr = mysql.format(sqlstr,params);
    connection.query(sqlstr,function (err,result) {
        var ret = {};
        if(err) {
            ret.IsSuccess = false;
            ret.Message = err;
            ret.Data = null;
        } else {
            ret.IsSuccess = true;
            ret.Message = '';
            ret.Data = {};
            ret.Data.UID = uid;
        }
        var jsonObj = JSON.stringify(ret);
        res.send(jsonObj);
    });
});

//删除用户
router.delete('/Users',function(req,res,next) {
    cros(req,res,next);
   var sqlstr ='delete from `User` where UID=?';
   var params = [];
   var uid = req.query.UID; 
   params.push(uid);
   sqlstr = mysql.format(sqlstr,params);
   console.log(sqlstr);
   connection.query(sqlstr,function (err,result) {
        var ret = {};
        if(err) {
            ret.IsSuccess = false;
            ret.Message = err;
            ret.Data = null;
        } else {
            ret.IsSuccess = true;
            ret.Message = '';
            ret.Data = {};
            ret.Data.UID = uid;
        }
        var jsonObj = JSON.stringify(ret);
        
        
        res.send(jsonObj);
    });
});

// 签证信息相关
router.get('/ShipMain',function name(req,res,next) {
    cros(req,res,next);
    var shipID = req.query.ShipID;
    var sqlstr = 'select * from ShipMain where ID=?';
    var params = [shipID];
    sqlstr = mysql.format(sqlstr,shipID);
    
    connection.query(sqlstr,function (err,rows,fileds) {
        var ret={};
        if(err) {
            ret.IsSuccess = false;
            ret.Message = err;
            ret.Data = null;
        } else {
            ret.IsSuccess = true;
            ret.Message = '';
            ret.Data = rows;
        }
        var jsonObj = JSON.stringify(ret);
        
        
        res.send(jsonObj);
    });
});

// Create New ShipMain
router.post('/ShipMain',function (req,res,next) {
    var CName = req.body.CName;
    var OwnPort = req.body.OwnPort;
    var ShipType = req.body.ShipType;
    var Type = req.body.Type;
    var TotalWeight = req.body.TotalWeight;
    var GlossWeight = req.body.GlossWeight;
    var Weight = req.body.Weight;
    var CustomCnt = req.body.CustomCnt;
    var Power = req.body.Power;
    var BusinessOwner = req.body.BusinessOwner;
    var MMSI = req.body.MMSI;
    var ContractPhoneNumber = req.body.ContractPhoneNumber;
    var CurrentPort = req.body.CurrentPort;
    var PortLocation = req.body.PortLocation;
    var DestPort = req.body.DestPort;
    var NextPort = req.body.NextPort;
    var StaffNumber = req.body.StaffNumber;
    var PortTime = req.body.PortTime;
    var FontDraft = req.body.FontDraft;
    var BackDraft = req.body.BackDraft;
    var VoyageNumber = req.body.VoyageNumber;
    var TotalCustomerCount = req.body.TotalCustomerCount;
    var TotalGoodsCount = req.body.TotalGoodsCount;
    var PortNewCustomer = req.body.PortNewCustomer;
    var PortNewGoods = req.body.PortNewGoods;
    var IsSecutity = req.body.IsSecutity;
    var BargeCount = req.body.BargeCount;
    var LoadBargeCount = req.body.LoadBargeCount;
    var ShipArea = req.body.ShipArea;
    var FeePrecent = req.body.FeePrecent;
    var PortFee = req.body.PortFee;
    var TradeMethod = req.body.TradeMethod;
    var CashMethod = req.body.CashMethod;
    var PortDelegate = req.body.PortDelegate;
    var ApplicationMan = req.body.ApplicationMan;
    var RunLine = req.body.RunLine;
    var VsaTime = req.body.VsaTime;
    var Mark = req.body.Mark;
    var StopHangZhou = req.body.StopHangZhou;
    var PassByHangZhou = req.body.PassByHangZhou;
    var Operater = req.body.Operater;
    var Authority = req.body.Authority;
    var sqlstr = "INSERT INTO `HaiShi`.`ShipMain`(`CName`,`OwnPort`,`ShipType`,`Type`,`TotalWeight`,`GlossWeight`,`Weight`," +
        "`CustomCnt`,`Power`,`BusinessOwner`,`MMSI`,`ContractPhoneNumber`,`CurrentPort`,`PortLocation`,`DestPort`,`NextPort`,"+
        "`StaffNumber`,`PortTime`,`FontDraft`,`BackDraft`,`VoyageNumber`,`TotalCustomerCount`,`TotalGoodsCount`,`PortNewCustomer`," + 
        "`PortNewGoods`,`IsSecutity`,`BargeCount`,`LoadBargeCount`,`ShipArea`,`FeePrecent`,`PortFee`,`TradeMethod`,`CashMethod`," + 
        "`PortDelegate`,`ApplicationMan`,`RunLine`,`VsaTime`,`Mark`,`StopHangZhou`,`PassByHangZhou`,`Operater`,`Authority`) VALUES(" + 
        "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; 
    var params = [];
    params.push(CName);
    params.push(OwnPort);
    params.push(ShipType);
    params.push(Type);
    params.push(TotalWeight);
    params.push(GlossWeight);
    params.push(Weight);
    params.push(CustomCnt);
    params.push(Power);
    params.push(BusinessOwner);
    params.push(MMSI);
    params.push(ContractPhoneNumber);
    params.push(CurrentPort);
    params.push(PortLocation);
    params.push(DestPort);
    params.push(NextPort);
    params.push(StaffNumber);
    params.push(PortTime);
    params.push(FontDraft);
    params.push(BackDraft);
    params.push(VoyageNumber);
    params.push(TotalCustomerCount);
    params.push(TotalGoodsCount);
    params.push(PortNewCustomer);
    params.push(PortNewGoods);
    params.push(IsSecutity);
    params.push(BargeCount);
    params.push(LoadBargeCount);
    params.push(ShipArea);
    params.push(FeePrecent);
    params.push(PortFee);
    params.push(TradeMethod);
    params.push(CashMethod);
    params.push(PortDelegate);
    params.push(ApplicationMan);
    params.push(RunLine);
    params.push(VsaTime);
    params.push(Mark);
    params.push(StopHangZhou);
    params.push(PassByHangZhou);
    params.push(Operater);
    params.push(Authority);

    console.dir(params);
    sqlstr= mysql.format(sqlstr,params);
    console.log(sqlstr);
   //var trans = connection.startTransaction();
    
    connection.query(sqlstr,function (err,result) {
        var ret = {};
        if(err) {
            ret.IsSuccess = false;
            ret.Message = err;
            ret.Data = null;
            var jsonObj = JSON.stringify(ret);
            res.send(jsonObj);
        } else {
            ret.IsSuccess = true;
            ret.Message = '';
            ret.Data = {};
            ret.Data.ShipID = result.insertId;
            var jsonObj = JSON.stringify(ret);
            res.send(jsonObj);
            
        }
    });
});


router.put('/ShipMain',function (req,res,next){
    cros(req,res,next);
    var CName = req.body.CName;
    var OwnPort = req.body.OwnPort;
    var ShipType = req.body.ShipType;
    var Type = req.body.Type;
    var TotalWeight = req.body.TotalWeight;
    var GlossWeight = req.body.GlossWeight;
    var Weight = req.body.Weight;
    var CustomCnt = req.body.CustomCnt;
    var Power = req.body.Power;
    var BusinessOwner = req.body.BusinessOwner;
    var MMSI = req.body.MMSI;
    var ContractPhoneNumber = req.body.ContractPhoneNumber;
    var CurrentPort = req.body.CurrentPort;
    var PortLocation = req.body.PortLocation;
    var DestPort = req.body.DestPort;
    var NextPort = req.body.NextPort;
    var StaffNumber = req.body.StaffNumber;
    var PortTime = req.body.PortTime;
    var FontDraft = req.body.FontDraft;
    var BackDraft = req.body.BackDraft;
    var VoyageNumber = req.body.VoyageNumber;
    var TotalCustomerCount = req.body.TotalCustomerCount;
    var TotalGoodsCount = req.body.TotalGoodsCount;
    var PortNewCustomer = req.body.PortNewCustomer;
    var PortNewGoods = req.body.PortNewGoods;
    var IsSecutity = req.body.IsSecutity;
    var BargeCount = req.body.BargeCount;
    var LoadBargeCount = req.body.LoadBargeCount;
    var ShipArea = req.body.ShipArea;
    var FeePrecent = req.body.FeePrecent;
    var PortFee = req.body.PortFee;
    var TradeMethod = req.body.TradeMethod;
    var CashMethod = req.body.CashMethod;
    var PortDelegate = req.body.PortDelegate;
    var ApplicationMan = req.body.ApplicationMan;
    var RunLine = req.body.RunLine;
    var VsaTime = req.body.VsaTime;
    var Mark = req.body.Mark;
    var StopHangZhou = req.body.StopHangZhou;
    var PassByHangZhou = req.body.PassByHangZhou;
    var Operater = req.body.Operater;
    var Authority = req.body.Authority;
    var ShipID = req.body.ID;

    var params = [];
    params.push(CName);
    params.push(OwnPort);
    params.push(ShipType);
    params.push(Type);
    params.push(TotalWeight);
    params.push(GlossWeight);
    params.push(Weight);
    params.push(CustomCnt);
    params.push(Power);
    params.push(BusinessOwner);
    params.push(MMSI);
    params.push(ContractPhoneNumber);
    params.push(CurrentPort);
    params.push(PortLocation);
    params.push(DestPort);
    params.push(NextPort);
    params.push(StaffNumber);
    params.push(PortTime);
    params.push(FontDraft);
    params.push(BackDraft);
    params.push(VoyageNumber);
    params.push(TotalCustomerCount);
    params.push(TotalGoodsCount);
    params.push(PortNewCustomer);
    params.push(PortNewGoods);
    params.push(IsSecutity);
    params.push(BargeCount);
    params.push(LoadBargeCount);
    params.push(ShipArea);
    params.push(FeePrecent);
    params.push(PortFee);
    params.push(TradeMethod);
    params.push(CashMethod);
    params.push(PortDelegate);
    params.push(ApplicationMan);
    params.push(RunLine);
    params.push(VsaTime);
    params.push(Mark);
    params.push(StopHangZhou);
    params.push(PassByHangZhou);
    params.push(Operater);
    params.push(Authority);
    params.push(ShipID);

    var sqlstr = "UPDATE `HaiShi`.`ShipMain` SET " + 
        "`CName` = ifnull(?,`CName`),"+
        "`OwnPort` = ifnull(?,`OwnPort`),"+
        "`ShipType` = ifnull(?,`ShipType`),"+
        "`Type` = ifnull(?,`Type`),"+
        "`TotalWeight` = ifnull(?,`TotalWeight`),"+
        "`GlossWeight` = ifnull(?,`GlossWeight`),"+
        "`Weight` = ifnull(?,`Weight`),"+
        "`CustomCnt` = ifnull(?,`CustomCnt`),"+
        "`Power` = ifnull(?,`Power`),"+
        "`BusinessOwner` = ifnull(?,`BusinessOwner`),"+
        "`MMSI` = ifnull(?,`MMSI`),"+
        "`ContractPhoneNumber` = ifnull(?,`ContractPhoneNumber`),"+
        "`CurrentPort` = ifnull(?,`CurrentPort`),"+
        "`PortLocation` = ifnull(?,`DestPort`),"+
        "`DestPort` = ifnull(?,`DestPort`),"+
        "`NextPort` = ifnull(?,`NextPort`),"+
        "`StaffNumber` = ifnull(?,`StaffNumber`),"+
        "`PortTime` = ifnull(?,`PortTime`),"+
        "`FontDraft` = ifnull(?,`FontDraft`),"+
        "`BackDraft` = ifnull(?,`BackDraft`),"+
        "`VoyageNumber` = ifnull(?,`VoyageNumber`),"+
        "`TotalCustomerCount` = ifnull(?,`TotalCustomerCount`),"+
        "`TotalGoodsCount` = ifnull(?,`TotalGoodsCount`),"+
        "`PortNewCustomer` = ifnull(?,`PortNewCustomer`),"+
        "`PortNewGoods` = ifnull(?,`PortNewGoods`),"+
        "`IsSecutity` = ifnull(?,`IsSecutity`),"+
        "`BargeCount` = ifnull(?,`BargeCount`),"+
        "`LoadBargeCount` =  ifnull(?,`LoadBargeCount`),"+
        "`ShipArea` = ifnull(?,`ShipArea`),"+
        "`FeePrecent` = ifnull(?,`FeePrecent`),"+
        "`PortFee` = ifnull(?,`PortFee`),"+
        "`TradeMethod` = ifnull(?,`TradeMethod`),"+
        "`CashMethod` = ifnull(?,`CashMethod`),"+
        "`PortDelegate` = ifnull(?,`PortDelegate`),"+
        "`ApplicationMan` = ifnull(?,`ApplicationMan`),"+
        "`RunLine` = ifnull(?,`RunLine`),"+
        "`VsaTime` = ifnull(?,`VsaTime`),"+
        "`Mark` = ifnull(?,`Mark`),"+
        "`StopHangZhou` = ifnull(?,`StopHangZhou`),"+
        "`PassByHangZhou` = ifnull(?,`PassByHangZhou`),"+
        "`Operater` =  ifnull(?,`Operater`),"+
        "`OperaterTime` = CURRENT_TIMESTAMP," +
        "`Authority` = ifnull(?,`Authority`)" +
        " WHERE `ID` = ?";

    console.dir(params);
    sqlstr= mysql.format(sqlstr,params);
    console.log(sqlstr);    
    connection.query(sqlstr,function (err,result) {
        var ret = {};
        if(err) {
            ret.IsSuccess = false;
            ret.Message = err;
            ret.Data = null;
        } else {
            ret.IsSuccess = true;
            ret.Message = '';
            ret.Data = {};
            ret.Data.ShipID = ShipID;
        }
        var jsonObj = JSON.stringify(ret);
        
        
        res.send(jsonObj);
    });
});

// 删除签证
router.delete('/ShipMain',function(req,res,next) {
    cros(req,res,next);
    var shipID = req.query.ShipID;
    console.log(shipID);
    var sqlstr = 'delete from ShipMain where ID=?';
    var params = [shipID];
    console.log(sqlstr);
    sqlstr = mysql.format(sqlstr,params);
    console.log(sqlstr);
    connection.query(sqlstr,function (err,result) {
        var ret = {};
        if(err) {
            ret.IsSuccess = false;
            ret.Message = err;
            ret.Data = null;
        } else {
            ret.IsSuccess = true;
            ret.Message = '';
            ret.Data = {};
            ret.Data.ShipID = shipID;
        }
        var jsonObj = JSON.stringify(ret);
        
        
        res.send(jsonObj);
    });
});


// 货物部分
// 根据类型获取货物
router.get('/ShipGoods',function (req,res,next) {
    cros(req,res,next);
    var shipID = req.query.ShipID;
    var Type =  req.query.Type;
    var sqlstr = 'select * from ShipGoods where ShipID=? and Type=?';
    var params = [shipID,Type];
    sqlstr = mysql.format(sqlstr,shipID);
    connection.query(sqlstr,function (err,rows,fileds) {
        var ret={};
        if(err) {
            ret.IsSuccess = false;
            ret.Message = err;
            ret.Data = null;
        } else {
            ret.IsSuccess = true;
            ret.Message = '';
            ret.Data = rows;
        }
        var jsonObj = JSON.stringify(ret);
        
        
        res.send(jsonObj);
    });
});

// 新增
router.post('/ShipGoods',function(req,res,next) {
    cros(req,res,next);
    var Type =  req.body.Type;
    var GoodsName = req.body.GoodsName;
    var shipID = req.body.ShipID;
    var sqlstr = 'insert into ShipGoods(`ShipID`,`GoodsName`,`Type`) values(?,?,?)';
    var params = [shipID,GoodsName,Type];
    sqlstr= mysql.format(sqlstr,params);
    console.log(sqlstr);
    connection.query(sqlstr,function (err,result) {
        var ret = {};        
        if(err) {
            ret.IsSuccess = false;
            ret.Message = err;
            ret.Data = null;
            var jsonObj = JSON.stringify(ret);
            res.send(jsonObj);
        } else {
            
            
            ret.IsSuccess = true;
            ret.Message = '';
            ret.Data = {};
            ret.Data.ShipID = result.insertId;
            var jsonObj = JSON.stringify(ret);
            
            res.send(jsonObj);
        }
        
    });
});

//更新货物
router.put('/ShipGoods',function(req,res,next) {
    cros(req,res,next);
    var Type =  req.body.Type;
    var GoodsName = req.body.GoodsName;
    var shipID = req.body.ShipID;
    var ID = req.body.ID;
    var sqlstr = 'update ShipGoods set `GoodsName`=ifnull(?,`GoodsName`),`Type`=ifnull(?,`Type`),`ShipID`=ifnull(?,`ShipID`) where ID=?';
    var params = [GoodsName,shipID,Type,ID];
    sqlstr= mysql.format(sqlstr,params);
    console.log(sqlstr);
    connection.query(sqlstr,function (err,result) {
        var ret = {};
        if(err) {
            ret.IsSuccess = false;
            ret.Message = err;
            ret.Data = null;
        } else {
            ret.IsSuccess = true;
            ret.Message = '';
            ret.Data = {};
            ret.Data.ShipID = ShipID;
        }
        var jsonObj = JSON.stringify(ret);
        
        
        res.send(jsonObj);
    });
});

// 删除货物
router.delete('/ShipGoods',function(req,res,next) {
    cros(req,res,next);
    var shipID = req.query.ID;
    console.log(shipID);
    var sqlstr = 'delete from ShipGoods where ID=?';
    var params = [shipID];
    console.log(sqlstr);
    sqlstr = mysql.format(sqlstr,params);
    console.log(sqlstr);
    connection.query(sqlstr,function (err,result) {
        var ret = {};
        if(err) {
            ret.IsSuccess = false;
            ret.Message = err;
            ret.Data = null;
        } else {
            ret.IsSuccess = true;
            ret.Message = '';
            ret.Data = {};
            ret.Data.ID = shipID;
        }
        var jsonObj = JSON.stringify(ret);
        res.send(jsonObj);
    });
});


// get one ship
router.get('/Ships',function (req,res,next) {
    cros(req,res,next);
    var shipID = req.query.ShipID;
    var sqlstr = 'select * from Ships where ID=?';
    var params = [shipID];
    sqlstr = mysql.format(sqlstr,shipID);
    
    connection.query(sqlstr,function (err,rows,fileds) {
        var ret={};
        if(err) {
            ret.IsSuccess = false;
            ret.Message = err;
            ret.Data = null;
        } else {
            ret.IsSuccess = true;
            ret.Message = '';
            ret.Data = rows;
        }
        var jsonObj = JSON.stringify(ret);
        
        
        res.send(jsonObj);
    });
});

router.post('/Ships',function(req,res,next) {
    cros(req,res,next);
   
  // console.dir(CertNo);
    
   var CName = req.body.CName;
   var EName = req.body.EName;
   var ShipClass = req.body.ShipClass;
   var IsDisable = req.body.IsDisable;
   var ShipRegisterNO = req.body.ShipRegisterNO;
   var FirstRegisterNO = req.body.FirstRegisterNO;
   var MMSI = req.body.MMSI;
   var CheckRegisterNO = req.body.CheckRegisterNO;
   var National = req.body.National;
   var CheckAutor = req.body.CheckAutor;
   var ShipHub = req.body.ShipHub;
   var ShipCategory = req.body.ShipCategory;
   var TotallWeight = req.body.TotallWeight;
   var Ower = req.body.Ower;
   var BusinssRunner = req.body.BusinssRunner;
   var ShipPower = req.body.ShipPower;
   var ShipLength = req.body.ShipLength;
   var ShipMaterial = req.body.ShipMaterial;
   var BoxAmount = req.body.BoxAmount;
   var SummerWeight = req.body.SummerWeight;
   var Speed = req.body.Speed;
   var IMC = req.body.IMC;
   var LincesNO = req.body.LincesNO;
   var Line = req.body.Line;
   var Area = req.body.Area;
   var MamagePart = req.body.MamagePart;
   var BuildingTime = req.body.BuildingTime;
   var GlossWeight = req.body.GlossWeight;
   var EngineerAmount = req.body.EngineerAmount;
   var ShipWidth = req.body.ShipWidth;
   var PushType = req.body.PushType;
   var Car = req.body.Car;
   var MinWorkSpace = req.body.MinWorkSpace;
   var MinSecurity = req.body.MinSecurity;
   var CallNO = req.body.CallNO;
   var CountryFlag = req.body.CountryFlag;
   var TotalOverLoadWeight = req.body.TotalOverLoadWeight;
   var EngineType = req.body.EngineType;
   var ShipTypeLength = req.body.ShipTypeLength;
   var PushAmount = req.body.PushAmount;
   var CustomAmount = req.body.CustomAmount;
   var AntiStromClass = req.body.AntiStromClass;
   var SurvyAmount = req.body.SurvyAmount;
   var Comment = req.body.Comment;
   var Creator = req.body.Creator;
   var CreateTime = req.body.CreateTime;
   var MainDepart = req.body.MainDepart;
   
   var sqlstr = 'INSERT INTO `Ships`(`CName`,`EName`,`ShipClass`,`IsDisable`,`ShipRegisterNO`,`FirstRegisterNO`,' +
    '`MMSI`,`CheckRegisterNO`,`National`,`CheckAutor`,`ShipHub`,`ShipCategory`,`TotallWeight`,`Ower`,`BusinssRunner`,' +
    '`ShipPower`,`ShipLength`,`ShipMaterial`,`BoxAmount`,`SummerWeight`,`Speed`,`IMC`,`LincesNO`,`Line`,`Area`,' +
    '`MamagePart`,`BuildingTime`,`GlossWeight`,`EngineerAmount`,`ShipWidth`,`PushType`,`Car`,`MinWorkSpace`,`MinSecurity`,' +
    '`CallNO`,`CountryFlag`,`TotalOverLoadWeight`,`EngineType`,`ShipTypeLength`,`PushAmount`,`CustomAmount`,' +
    '`AntiStromClass`,`SurvyAmount`,`Comment`,`Creator`,`CreateTime`,`MainDepart`) VALUES(' +
    '?,?,?,?,?,?,' +
    '?,?,?,?,?,?,?,?,?,' +
    '?,?,?,?,?,?,?,?,?,?,' +
    '?,?,?,?,?,?,?,?,?,' +
    '?,?,?,?,?,?,?,' +
    '?,?,?,?,?,?' +
    ')';
    
    var params = [];
    params.push(CName);
    params.push(EName);
    params.push(ShipClass);
    params.push(IsDisable);
    params.push(ShipRegisterNO);
    params.push(FirstRegisterNO);
    params.push(MMSI);
    params.push(CheckRegisterNO);
    params.push(National);
    params.push(CheckAutor);
    params.push(ShipHub);
    params.push(ShipCategory);
    params.push(TotallWeight);
    params.push(Ower);
    params.push(BusinssRunner);
    params.push(MamagePart);
    params.push(ShipLength);
    params.push(ShipMaterial);
    params.push(BoxAmount);
    params.push(SummerWeight);
    params.push(Speed);
    params.push(IMC);
    params.push(LincesNO);
    params.push(Line);
    params.push(Area);
    params.push(MamagePart);
    params.push(BuildingTime);
    params.push(GlossWeight);
    params.push(EngineerAmount);
    params.push(ShipWidth);
    params.push(PushType);
    params.push(Car);
    params.push(MinWorkSpace);
    params.push(MinSecurity);
    params.push(CallNO);
    params.push(CountryFlag);
    params.push(TotalOverLoadWeight);
    params.push(EngineType);
    params.push(ShipTypeLength);
    params.push(PushAmount);
    params.push(CustomAmount);
    params.push(AntiStromClass);
    params.push(SurvyAmount);
    params.push(Comment);
    params.push(Creator);
    params.push(CreateTime);
    params.push(MainDepart);
    console.dir(params);
    sqlstr= mysql.format(sqlstr,params);
    console.log(sqlstr);
   //var trans = connection.startTransaction();
    
    connection.query(sqlstr,function (err,result) {
        var ret = {};        
        if(err) {
            ret.IsSuccess = false;
            ret.Message = err;
            ret.Data = null;
            var jsonObj = JSON.stringify(ret);
            res.send(jsonObj);
        } else {
            
            
            ret.IsSuccess = true;
            ret.Message = '';
            ret.Data = {};
            ret.Data.ShipID = result.insertId;
            var jsonObj = JSON.stringify(ret);
            
            
            res.send(jsonObj);
            
        }
        
    });
   
});


router.put('/Ships',function(req,res,next) {
    cros(req,res,next);
    var CName = req.body.CName;
   var EName = req.body.EName;
   var ShipClass = req.body.ShipClass;
   var IsDisable = req.body.IsDisable;
   var ShipRegisterNO = req.body.ShipRegisterNO;
   var FirstRegisterNO = req.body.FirstRegisterNO;
   var MMSI = req.body.MMSI;
   var CheckRegisterNO = req.body.CheckRegisterNO;
   var National = req.body.National;
   var CheckAutor = req.body.CheckAutor;
   var ShipHub = req.body.ShipHub;
   var ShipCategory = req.body.ShipCategory;
   var TotallWeight = req.body.TotallWeight;
   var Ower = req.body.Ower;
   var BusinssRunner = req.body.BusinssRunner;
   var ShipPower = req.body.ShipPower;
   var ShipLength = req.body.ShipLength;
   var ShipMaterial = req.body.ShipMaterial;
   var BoxAmount = req.body.BoxAmount;
   var SummerWeight = req.body.SummerWeight;
   var Speed = req.body.Speed;
   var IMC = req.body.IMC;
   var LincesNO = req.body.LincesNO;
   var Line = req.body.Line;
   var Area = req.body.Area;
   var MamagePart = req.body.MamagePart;
   var BuildingTime = req.body.BuildingTime;
   var GlossWeight = req.body.GlossWeight;
   var EngineerAmount = req.body.EngineerAmount;
   var ShipWidth = req.body.ShipWidth;
   var PushType = req.body.PushType;
   var Car = req.body.Car;
   var MinWorkSpace = req.body.MinWorkSpace;
   var MinSecurity = req.body.MinSecurity;
   var CallNO = req.body.CallNO;
   var CountryFlag = req.body.CountryFlag;
   var TotalOverLoadWeight = req.body.TotalOverLoadWeight;
   var EngineType = req.body.EngineType;
   var ShipTypeLength = req.body.ShipTypeLength;
   var PushAmount = req.body.PushAmount;
   var CustomAmount = req.body.CustomAmount;
   var AntiStromClass = req.body.AntiStromClass;
   var SurvyAmount = req.body.SurvyAmount;
   var Comment = req.body.Comment;
   var Creator = req.body.Creator;
   var CreateTime = req.body.CreateTime;
   var MainDepart = req.body.MainDepart;
   var ShipID = req.body.ID;
   
   
   var params = [];
    params.push(CName);
    params.push(EName);
    params.push(ShipClass);
    params.push(IsDisable);
    params.push(ShipRegisterNO);
    params.push(FirstRegisterNO);
    params.push(MMSI);
    params.push(CheckRegisterNO);
    params.push(National);
    params.push(CheckAutor);
    params.push(ShipHub);
    params.push(ShipCategory);
    params.push(TotallWeight);
    params.push(Ower);
    params.push(BusinssRunner);
    params.push(MamagePart);
    params.push(ShipLength);
    params.push(ShipMaterial);
    params.push(BoxAmount);
    params.push(SummerWeight);
    params.push(Speed);
    params.push(IMC);
    params.push(LincesNO);
    params.push(Line);
    params.push(Area);
    params.push(MamagePart);
    params.push(BuildingTime);
    params.push(GlossWeight);
    params.push(EngineerAmount);
    params.push(ShipWidth);
    params.push(PushType);
    params.push(Car);
    params.push(MinWorkSpace);
    params.push(MinSecurity);
    params.push(CallNO);
    params.push(CountryFlag);
    params.push(TotalOverLoadWeight);
    params.push(EngineType);
    params.push(ShipTypeLength);
    params.push(PushAmount);
    params.push(CustomAmount);
    params.push(AntiStromClass);
    params.push(SurvyAmount);
    params.push(Comment);
    params.push(Creator);
    params.push(CreateTime);
    params.push(MainDepart);
    params.push(ShipID);
    console.dir(params);
    var sqlstr = 'UPDATE `Ships` SET ' +
        '`CName` = ifnull(?,CName),' +
        '`EName` = ifnull(?,EName),' +
        '`ShipClass` = ifnull(?,ShipClass),' +
        '`IsDisable` = ifnull(?,IsDisable),' +
        '`ShipRegisterNO` = ifnull(?,ShipRegisterNO),' +
        '`FirstRegisterNO` =ifnull(?,FirstRegisterNO),' +
        '`MMSI` = ifnull(?,MMSI),' +
        '`CheckRegisterNO` = ifnull(?,CheckRegisterNO),' +
        '`National` = ifnull(?,National),' +
        '`CheckAutor` = ifnull(?,CheckAutor),' +
        '`ShipHub` = ifnull(?,ShipHub),' +
        '`ShipCategory` = ifnull(?,ShipCategory),' +
        '`TotallWeight` = ifnull(?,TotallWeight),' +
        '`Ower` = ifnull(?,Ower),' +
        '`BusinssRunner` = ifnull(?,BusinssRunner),' +
        '`ShipPower` = ifnull(?,ShipPower),' +
        '`ShipLength` = ifnull(?,ShipLength),' +
        '`ShipMaterial` = ifnull(?,ShipMaterial),' +
        '`BoxAmount` = ifnull(?,BoxAmount),' +
        '`SummerWeight` = ifnull(?,SummerWeight),' +
        '`Speed` = ifnull(?,Speed),' +
        '`IMC` = ifnull(?,IMC),' + 
        '`LincesNO` = ifnull(?,LincesNO),'+
        '`Line` = ifnull(?,Line),' +
        '`Area` = ifnull(?,Area),' +
        '`MamagePart` = ifnull(?,MamagePart),' +
        '`BuildingTime` = ifnull(?,BuildingTime),' +
        '`GlossWeight` = ifnull(?,GlossWeight),' +
        '`EngineerAmount` = ifnull(?,EngineerAmount),' +
        '`ShipWidth` = ifnull(?,ShipWidth),' +
        '`PushType` = ifnull(?,PushType),' +
        '`Car` = ifnull(?,Car),' +
        '`MinWorkSpace` = ifnull(?,MinWorkSpace),' +
        '`MinSecurity` = ifnull(?,MinSecurity),' +
        '`CallNO` = ifnull(?,CallNO),' + 
        '`CountryFlag` = ifnull(?,CountryFlag),' +
        '`TotalOverLoadWeight` = ifnull(?,TotalOverLoadWeight),' + 
        '`EngineType` = ifnull(?,EngineType),' +
        '`ShipTypeLength` = ifnull(?,ShipTypeLength),' +
        '`PushAmount` = ifnull(?,PushAmount),' + 
        '`CustomAmount` = ifnull(?,CustomAmount),' +
        '`AntiStromClass` = ifnull(?,AntiStromClass),' +
        '`SurvyAmount` = ifnull(?,SurvyAmount),' +
        '`Comment` = ifnull(?,Comment),' +
        '`Creator` = ifnull(?,Creator),' +
        '`CreateTime` = ifnull(?,CreateTime),' +
         '`MainDepart` = ifnull(?,MainDepart)' + 
        ' WHERE `ID` = ?';
    sqlstr= mysql.format(sqlstr,params);
    console.log(sqlstr);
   
    connection.query(sqlstr,function (err,result) {
        var ret = {};
        if(err) {
            ret.IsSuccess = false;
            ret.Message = err;
            ret.Data = null;
        } else {
            ret.IsSuccess = true;
            ret.Message = '';
            ret.Data = {};
            ret.Data.ShipID = ShipID;
        }
        var jsonObj = JSON.stringify(ret);
        
        
        res.send(jsonObj);
    });
});

router.delete('/Ships',function(req,res,next) {
    cros(req,res,next);
    var shipID = req.query.ShipID;
    console.log(shipID);
    var sqlstr = 'delete from Ships where ID=?';
    var params = [shipID];
    console.log(sqlstr);
    sqlstr = mysql.format(sqlstr,params);
    console.log(sqlstr);
    connection.query(sqlstr,function (err,result) {
        var ret = {};
        if(err) {
            ret.IsSuccess = false;
            ret.Message = err;
            ret.Data = null;
        } else {
            ret.IsSuccess = true;
            ret.Message = '';
            ret.Data = {};
            ret.Data.ShipID = shipID;
        }
        var jsonObj = JSON.stringify(ret);
        
        
        res.send(jsonObj);
    });
});

// 查询船的信息
router.post('/ShipQuery',function(req,res,next) {
   cros(req,res,next);
   var keyword = req.body.keyword;
   if(keyword==undefined) {
       keyword = keyword.trim();
   } 
   var registerNO = req.body.registerNO;
   var sqlstr = "select * from Ships where (CName like '%" + keyword + "%' or EName like '%" + keyword + "%')";
   if(registerNO!=undefined && registerNO.trim()!='') {
       sqlstr = sqlstr + ' and ShipRegisterNO=?';
       sqlstr = mysql.format(sqlstr,[registerNO]);
      
   }
   connection.query(sqlstr,function (err,rows,fileds) {
        var ret = {};
        if(err) {
            ret.IsSuccess = false;
            ret.Message = err;
            ret.Data = null;
        } else {
            ret.IsSuccess = true;
            ret.Message = '';
            ret.Data = rows;
        }
        var jsonObj = JSON.stringify(ret);
        
        
        res.send(jsonObj);
    });
});

// 查询ShipMain
router.post('/ShipMainQuery',function(req,res,next) {
   cros(req,res,next);
   var keyword = req.body.keyword;
   if(keyword==undefined) {
       keyword = keyword.trim();
   } 
   var registerNO = req.body.registerNO;
   var sqlstr = "select * from ShipMain where CName like '%" + keyword +  "%'";
   if(registerNO!=undefined && registerNO.trim()!='') {
       sqlstr = sqlstr + ' and MMSI=?';
       sqlstr = mysql.format(sqlstr,[registerNO]);
      
   }
   connection.query(sqlstr,function (err,rows,fileds) {
        var ret = {};
        if(err) {
            ret.IsSuccess = false;
            ret.Message = err;
            ret.Data = null;
        } else {
            ret.IsSuccess = true;
            ret.Message = '';
            ret.Data = rows;
        }
        var jsonObj = JSON.stringify(ret);
        
        
        res.send(jsonObj);
    });
});


// 获取船员资质信息
router.get('/ShipCert',function(req,res,next) {
    cros(req,res,next);
    var shipID = req.query.ShipID;
    var sqlstr = 'select * from ShipCert where ShipID=?';
    var params = [shipID];
    sqlstr = mysql.format(sqlstr,shipID);
    
    connection.query(sqlstr,function (err,rows,fileds) {
        var ret={};
        if(err) {
            ret.IsSuccess = false;
            ret.Message = err;
            ret.Data = null;
        } else {
            ret.IsSuccess = true;
            ret.Message = '';
            ret.Data = rows;
        }
        var jsonObj = JSON.stringify(ret);
        
        
        res.send(jsonObj);
    })

});

// 新建船员资质信息
router.post('/ShipCert',function(req,res,next) { 
    var shipID = req.body.ShipID;
    var CertName = req.body.CertName;
    var IsParment = req.body.IsParment;
    var params = [CertName,IsParment,shipID];
    var sqlstr = 'insert into ShipCert(CertName,IsParment,ShipID) values(?,?,?)';
    
    sqlstr = mysql.format(sqlstr,params);
    console.log(sqlstr);
    connection.query(sqlstr,function (err,result) {
        var ret = {};
        if(err) {
            ret.IsSuccess = false;
            ret.Message = err;
            ret.Data = null;
        } else {
            ret.IsSuccess = true;
            ret.Message = '';
            ret.Data = {};
            ret.Data.ShipID = result.insertId;
        }
        var jsonObj = JSON.stringify(ret);
        res.send(jsonObj);
    });
});

// 更新船员资质信息
router.put('/ShipCert',function(req,res,next) { 
    var CertName = req.body.CertName;
    var IsParment = req.body.IsParment;
    var ShipCertID = req.body.ShipCertID;
    var params = [CertName,IsParment,ShipCertID];
    var sqlstr = 'update ShipCert set CertName=ifnull(?,CertName),IsParment=ifnull(?,IsParment) where ID=?';
    sqlstr = mysql.format(sqlstr,params);
    connection.query(sqlstr,function (err,result) {
        var ret = {};
        if(err) {
            ret.IsSuccess = false;
            ret.Message = err;
            ret.Data = null;
        } else {
            ret.IsSuccess = true;
            ret.Message = '';
            ret.Data = {};
            ret.Data.ShipID = ShipCertID;
        }
        var jsonObj = JSON.stringify(ret);
        res.send(jsonObj);
    });
});

// 删除船员资质信息
router.delete('/ShipCert',function(req,res,next) { 
    var ShipCertID = req.query.ShipCertID;
    var params = [ShipCertID];
    var sqlstr = 'delete from ShipCert where ID=?';
    sqlstr = mysql.format(sqlstr,params);
    connection.query(sqlstr,function (err,result) {
        var ret = {};
        if(err) {
            ret.IsSuccess = false;
            ret.Message = err;
            ret.Data = null;
        } else {
            ret.IsSuccess = true;
            ret.Message = '';
            ret.Data = {};
            ret.Data.ShipID = ShipCertID;
        }
        var jsonObj = JSON.stringify(ret);
        res.send(jsonObj);
    });
});

// 查询船员信息
router.get('/ShipStaff',function(req,res,next) {
    cros(req,res,next);
    var shipID = req.query.ShipID;
    var sqlstr = 'select * from ShipStaff where ShipID=?';
    var params = [shipID];
    sqlstr = mysql.format(sqlstr,shipID);
    
    connection.query(sqlstr,function (err,rows,fileds) {
        var ret={};
        if(err) {
            ret.IsSuccess = false;
            ret.Message = err;
            ret.Data = null;
        } else {
            ret.IsSuccess = true;
            ret.Message = '';
            ret.Data = rows;
        }
        var jsonObj = JSON.stringify(ret);
        
        
        res.send(jsonObj);
    })

});

// 新建船员信息
router.post('/ShipStaff',function(req,res,next) {
    cros(req,res,next);
    var Duty = req.body.Duty;
    var CertNo = req.body.CertNo;
    var shipID = req.body.ShipID;
    var params = [Duty,CertNo,shipID];
    var sqlstr = 'insert into ShipStaff(Duty,CertNo,ShipID) values(?,?,?)';
    sqlstr = mysql.format(sqlstr,params);
    connection.query(sqlstr,function (err,result) {
        var ret = {};
        if(err) {
            ret.IsSuccess = false;
            ret.Message = err;
            ret.Data = null;
        } else {
            ret.IsSuccess = true;
            ret.Message = '';
            ret.Data = {};
            ret.Data.ShipID = result.insertId;
        }
        var jsonObj = JSON.stringify(ret);
        res.send(jsonObj);
    });
});

// 更新船员信息
router.put('/ShipStaff',function(req,res,next) {
    cros(req,res,next);
    var Duty = req.body.Duty;
    var CertNo = req.body.CertNo;
    var shipStaffID = req.body.StaffID;
    var params = [Duty,CertNo,shipID];
    var sqlstr = 'update ShipStaff set Duty=ifnull(?,Duty), ifnull(?,CertNo) where ID=?';
    sqlstr = mysql.format(sqlstr,params);
    connection.query(sqlstr,function (err,result) {
        var ret = {};
        if(err) {
            ret.IsSuccess = false;
            ret.Message = err;
            ret.Data = null;
        } else {
            ret.IsSuccess = true;
            ret.Message = '';
            ret.Data = {};
            ret.Data.ShipID = result.insertId;
        }
        var jsonObj = JSON.stringify(ret);
        res.send(jsonObj);
    });
});

// 删除船员信息
router.delete('/ShipStaff',function(req,res,next) { 
    var StaffID = req.query.StaffID;
    var params = [StaffID];
    var sqlstr = 'delete from ShipStaff where ID=?';
    sqlstr = mysql.format(sqlstr,params);
    connection.query(sqlstr,function (err,result) {
        var ret = {};
        if(err) {
            ret.IsSuccess = false;
            ret.Message = err;
            ret.Data = null;
        } else {
            ret.IsSuccess = true;
            ret.Message = '';
            ret.Data = {};
            ret.Data.ShipID = StaffID;
        }
        var jsonObj = JSON.stringify(ret);
        res.send(jsonObj);
    });
});


// 导入数据
var type = upload.single('ImportFile');
router.post('/Import',type,function(req,res,next) {
    var tmp_path = req.file.path;

    /** The original name of the uploaded file
         stored in the variable "originalname". **/
    var target_path = '/tmp/' + uuid.v1()  + '.xlsx';

    /** A better way to copy the uploaded file. **/
    var src = fs.createReadStream(tmp_path);
    var dest = fs.createWriteStream(target_path);
    src.pipe(dest);
    var result = {};
    src.on('end', function() 
    { 
         //var path = '/Users/lokiewang/Desktop/test.xlsx';
        console.log(target_path);
        var workbook = XLSX.readFile(target_path);
        //console.log(workbook);
        var sheet_name_list = workbook.SheetNames;
        var worksheet = workbook.Sheets[sheet_name_list[0]];
        var headers = {};
        var data = [];
        for(z in worksheet) {
            if(z[0] === '!') continue;
            //parse out the column, row, and value
            var tt = 0;
            for (var i = 0; i < z.length; i++) {
                if (!isNaN(z[i])) {
                    tt = i;
                    break;
                }
            };
            var col = z.substring(0,tt);
            var row = parseInt(z.substring(tt));
            var value = worksheet[z].v;

            //store header names
            if(row == 1 && value) {
                headers[col] = value;
                continue;
            }

            if(!data[row]) data[row]={};
            data[row][headers[col]] = value;
        }
        //drop those first two rows which are empty
        data.shift();
        data.shift();
        for(var i=0;i<data.length;i++) {
            var input = data[i];
            var CName = isNull(input['中文船名']);
            var OwnPort = isNull(input['船籍港']);
            var ShipType = isNull(input['船舶类型']);
            var Type = isNull(input['船舶种类']);
            var TotalWeight = isNull(input['总吨']);
            var GlossWeight = isNull(input['净吨']);
            var Weight = isNull(input['净吨']);
            var CustomCnt = isNull(input['客位']);
            var Power = isNull(input['主机功率']);
            var BusinessOwner = isNull(input['所有人']);
            var MMSI = isNull(input['MMSI']);
            var ContractPhoneNumber = null;
            var CurrentPort = isNull(input['港口']);
            var PortLocation = isNull(input['泊位']);
            var DestPort = isNull(input['目的港']);
            var NextPort = isNull(input['下一港']);
            var StaffNumber = isNull(input['船员人数']);
            var PortTime = isNull(input['出港时间']);
            var FontDraft = isNull(input['吃水前']);
            var BackDraft = isNull(input['吃水后']);
            var VoyageNumber = isNull(input['航次']);
            var TotalCustomerCount = isNull(input['实载客量']);
            var TotalGoodsCount = isNull(input['实载货量']);
            var PortNewCustomer = isNull(input['本港上客']);
            var PortNewGoods = isNull(input['本港装货']);
            var IsSecutity = isNull(input['装载危险货物']);
            var BargeCount = isNull(input['驳船数量']);
            var LoadBargeCount = isNull(input['加载驳船']);
            var ShipArea = isNull(input['航区']);
            var FeePrecent = isNull(input['费率']);
            var PortFee = isNull(input['港务费']);
            var TradeMethod = isNull(input['付款方式']);
            var CashMethod = isNull(input['收款方式']);
            var PortDelegate = isNull(input['出港代理']);
            var ApplicationMan = isNull(input['申办人']);
            var RunLine = isNull(input["经营航线"]);
            var VsaTime = isNull(input['签证时间']);
            var Mark = isNull(input['备注']);
            var StopHangZhou = isNull(input['驳靠杭州水域']);
            var PassByHangZhou = isNull(input['途径杭州水域']);
            var Operater = isNull(input['操作员']);
            var Authority = isNull(input['所属机构']);
            var sqlstr = "INSERT INTO `HaiShi`.`ShipMain`(`CName`,`OwnPort`,`ShipType`,`Type`,`TotalWeight`,`GlossWeight`,`Weight`," +
                "`CustomCnt`,`Power`,`BusinessOwner`,`MMSI`,`ContractPhoneNumber`,`CurrentPort`,`PortLocation`,`DestPort`,`NextPort`,"+
                "`StaffNumber`,`PortTime`,`FontDraft`,`BackDraft`,`VoyageNumber`,`TotalCustomerCount`,`TotalGoodsCount`,`PortNewCustomer`," + 
                "`PortNewGoods`,`IsSecutity`,`BargeCount`,`LoadBargeCount`,`ShipArea`,`FeePrecent`,`PortFee`,`TradeMethod`,`CashMethod`," + 
                "`PortDelegate`,`ApplicationMan`,`RunLine`,`VsaTime`,`Mark`,`StopHangZhou`,`PassByHangZhou`,`Operater`,`Authority`) VALUES(" + 
                "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; 

            var params = [];
            params.push(CName);
            params.push(OwnPort);
            params.push(ShipType);
            params.push(Type);
            params.push(TotalWeight);
            params.push(GlossWeight);
            params.push(Weight);
            params.push(CustomCnt);
            params.push(Power);
            params.push(BusinessOwner);
            params.push(MMSI);
            params.push(ContractPhoneNumber);
            params.push(CurrentPort);
            params.push(PortLocation);
            params.push(DestPort);
            params.push(NextPort);
            params.push(StaffNumber);
            params.push(PortTime);
            params.push(FontDraft);
            params.push(BackDraft);
            params.push(VoyageNumber);
            params.push(TotalCustomerCount);
            params.push(TotalGoodsCount);
            params.push(PortNewCustomer);
            params.push(PortNewGoods);
            params.push(IsSecutity);
            params.push(BargeCount);
            params.push(LoadBargeCount);
            params.push(ShipArea);
            params.push(FeePrecent);
            params.push(PortFee);
            params.push(TradeMethod);
            params.push(CashMethod);
            params.push(PortDelegate);
            params.push(ApplicationMan);
            params.push(RunLine);
            params.push(VsaTime);
            params.push(Mark);
            params.push(StopHangZhou);
            params.push(PassByHangZhou);
            params.push(Operater);
            params.push(Authority);

            // console.dir(params);
            sqlstr= mysql.format(sqlstr,params);
            // console.log(sqlstr);
            connection.query(sqlstr,function (err,rows,fields) {
                if(err) { 
                    console.log(err);  
                    result.IsSuccess = false;
                    result.Message = 'error';
                }
            });
        }
        result.IsSuccess = true;
        result.Message = '';
        res.send(JSON.stringify(result));
    });
    src.on('error', function(err) 
    { 
        result.IsSuccess = false;
        result.Message = '文件上传失败';
        res.send(JSON.stringify(result));
    });
});

function isNull(param) {
    if(param == undefined) {
        return '';
    } else {
        return param;
    }
}


module.exports = router;


